﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using Defarm.Model;
using Defarm.Model.Common;
using Defarm.Model.DB;

namespace Defarm.Helpers
{
    public static class DbHelper
    {
        #region Constants

        private static readonly string ConnectionString = "Data Source=(local)\\SQLEXPRESS;" +
                                                          "Initial Catalog=DefarmDB;" +
                                                          "Integrated Security=True;";

        #endregion

        #region Public Methods

        public static Chemist CheckCredentials(string login, string password)
        {
            var dt =
                GetDataTable(
                    "SELECT Id, Name, CityId, OrganisationId FROM DefarmDB.dbo.Chemist WHERE Login ='" + login + "' AND Password = '" + password + "'",
                    ConnectionString);

            var chemists = (from DataRow row in dt.Rows select ConvertDataRowToChemist(row)).ToList();

            if (chemists.Count > 0)
            {
                return chemists[0];
            }

            return null;
        }

        public static Chemist GetCityOrganisation(Chemist chemist)
        {
            var dt =
                GetDataTable(
                    "SELECT CityId, OrganisationId FROM DefarmDB.dbo.Chemist WHERE Id = '" + chemist.Id + "'",
                    ConnectionString);

            var ids = (from DataRow row in dt.Rows select ConvertDataRowToIds(row)).ToList();

            var chemistToReturn = chemist;

            dt =
                GetDataTable(
                    "SELECT Name FROM DefarmDB.dbo.City WHERE Id = '" + ids[0].CityId + "'",
                    ConnectionString);

            var temp = (from DataRow row in dt.Rows select ConvertDataRowToName(row)).ToList();

            chemistToReturn.City = temp[0];

            dt =
                GetDataTable(
                    "SELECT Name FROM DefarmDB.dbo.Organisation WHERE Id = '" + ids[0].OrganisationId + "'",
                    ConnectionString);
            
            temp = (from DataRow row in dt.Rows select ConvertDataRowToName(row)).ToList();

            chemistToReturn.Organisation = temp[0];

            return chemistToReturn;
        }

        public static List<Position> GetPositionsFromBase()
        {
            var dt = GetDataTable("SELECT * FROM DefarmDB.dbo.Position", ConnectionString);

            return (from DataRow row in dt.Rows select ConvertDataRowToPosition(row)).ToList();
        }

        #endregion

        #region Private Methods

        private static DataTable GetDataTable(string query, string connectionString)
        {
            var dt = new DataTable();

            using (SqlConnection sqlConn = new SqlConnection(connectionString))
            {
                using (SqlDataAdapter sqlAdapter = new SqlDataAdapter(query, sqlConn))
                {
                    sqlAdapter.Fill(dt);
                }
            }

            return dt;
        }

        private static Chemist ConvertDataRowToChemist(DataRow row)
        {
            return new Chemist
            {
                Id = Guid.Parse(row["Id"].ToString()),
                Name = row["Name"].ToString()
            };
        }

        private static CityOrganisationIds ConvertDataRowToIds(DataRow row)
        {
            return new CityOrganisationIds
            {
                CityId = Convert.ToInt32(row["CityId"]),
                OrganisationId = Convert.ToInt32(row["OrganisationId"])
            };
        }

        private static string ConvertDataRowToName(DataRow row)
        {
            return row["Name"].ToString();
        }

        private static Position ConvertDataRowToPosition(DataRow row)
        {
            return new Position
            {
                Id = Guid.Parse(row["Id"].ToString()),
                Name = row["Name"].ToString()
            };
        }

        #endregion
    }

    public class CityOrganisationIds
    {
        public int CityId { get; set; }

        public int OrganisationId { get; set; }
    }
}
